The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.
One of the most successful analytical tools is a spreadsheet. A user can enter business data, define formulas over it using two-dimensional array abstractions, construct simultaneous equations with recursive models, pivot data, compute aggregates for selected cells, and apply a rich set of business functions. Some spreadsheets also provide a flexible user interface with various graphical elements, such as graphs, reports, and charts.
Unfortunately, the analytical usefulness of conventional Database Management Systems (DBMS) has not measured up to that of spreadsheets or specialized MOLAP (Multidimensional OnLine Analytical Processing) tools. Traditionally, the analytical capabilities of a DBMS have been cumbersome and in most cases inefficient to perform array-like calculations in SQL—a fundamental problem resulting from a lack of language constructs to treat relations as arrays and to define formulas over them and a lack of efficient random access methods for inter-row calculations.
To address the gaps in the analytical capabilities of conventional DBMS, SQL has recently been extended to include language constructs, referred to herein as “spreadsheet extensions”, that allow relations to be treated as n-dimensional arrays and that allow formulas to be defined in terms of the elements of the arrays. The formulas, which are referred to hereinafter as “rules”, are encapsulated in a new SQL clause. This new SQL clause is referred to hereinafter as a “model” clause. A SQL model clause included in a query supports partitioning of the data in the relation that is generated as a result of executing the query. (The term “query” as used herein refers to a statement that conforms to a query language, such as SQL, and specifies operations to manipulate data.)
The spreadsheet extensions of a query language supported by a DBMS provide for defining and executing spreadsheet-like operations on data managed by the DBMS. The spreadsheet extensions provide an improvement over traditional spreadsheet programs by integrating their analytical functions into a DBMS, thus improving readability with symbolic referencing and providing greater scalability and improved manageability of data, security, storage space, and other parameters that are managed by the DBMS. Additionally, the spreadsheet extensions provide a clear improvement over conventional database query languages by providing spreadsheet-like computations directly within the language.